/*
 * AccettaProdottiServlet
 * 
 * Author: Carmine Colicino, Luigi Di Martino
 * Version: 1.0
 * 
 * 2014 - Copyright by Team 6 - University of Salerno
*/

package it.unisa.udocs.storage;

import it.unisa.udocs.commons.DBConnectionPool;
import it.unisa.udocs.commons.DBNames;
import it.unisa.udocs.data.Account;
import it.unisa.udocs.data.Prodotto;
import it.unisa.udocs.data.Ricercatore;
import it.unisa.udocs.data.Amministratore;
import it.unisa.udocs.data.ResponsabileAteneo;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mysql.jdbc.Statement;



public class DBAccount {

	private static DBAccount dbAccount;

	/**
	 * Method that implements the Singleton Design Pattern
	 * 
	 * @return DBAccount
	 * 
	 */
	public static DBAccount getInstance() {
		if (dbAccount == null)
			dbAccount = new DBAccount();
		return dbAccount;
	}


	/**
	 * Is used to search for  all researchers in our System
	 * 
	 * @return The list of all researchers stored in the System 
	 * 
	 */
	public ArrayList<Account> getAllRicercatori()  throws SQLException{

		ArrayList<Account> listaAccount = new ArrayList<Account>();

		Connection con= DBConnectionPool.getConnection();
		Account account=null;
		String query="SELECT * FROM ACCOUNT, RICERCATORE where "
				+ "account.id_account = ricercatore.id_account AND tipologia <>"
				+ " 'amministratore'" ;

		Statement st=null;
		ResultSet rs=null;

		st = (Statement) con.createStatement();
		rs=st.executeQuery(query);

		while(rs.next()) {
			account = new Ricercatore(
					Integer.parseInt(rs.getString(DBNames.ACC_ID)),
					rs.getString(DBNames.ACC_USER),
					rs.getString(DBNames.ACC_PSW),
					rs.getString(DBNames.ACC_TIPOLOGIA),
					rs.getString(DBNames.RICER_NOME),
					rs.getString(DBNames.RICER_COGN),
					rs.getString(DBNames.RICER_COD_FISC),
					rs.getDate(DBNames.RICER_DATA_NASC),
					rs.getString(DBNames.RICER_CITTA_NASC),
					rs.getString(DBNames.RICER_SEX),
					rs.getString(DBNames.RICER_INDIRIZZO),
					rs.getString(DBNames.RICER_EMAIL),
					rs.getString(DBNames.RICER_RUOLO),
					rs.getString(DBNames.RICER_MATRIC),
					rs.getInt(DBNames.RICER_ANNO_MES_RUOLO),
					rs.getInt(DBNames.RICER_DIPART),
					rs.getInt(DBNames.RICER_AREA_SCI)
					);
			listaAccount.add(account);
		}

		return listaAccount;

	}

	/**
	 * Is used to search for an Account in our System
	 * 
	 * @param  username - The username
	 * @param  password - The password
	 *  @return The account found in the System
	 * 
	 */
	public Account cercaAccount(String username, 
			String password) throws SQLException {

		Account account = null;

		Statement st = null;
		ResultSet rs = null;

		int id_account = 0;
		String tipologia = "";

		Connection con = DBConnectionPool.getConnection();

		String query = "select * from account where username = '"
				+username+"' and password = '"+password+"'";

		st = (Statement) con.createStatement();
		rs = st.executeQuery(query);

		if(rs.next()) {
			id_account = rs.getInt(DBNames.ACC_ID);
			tipologia = rs.getString(DBNames.ACC_TIPOLOGIA);			
		}


		if(tipologia.equals("ricercatore")) {
			//La query restituisce i campi del ricercatore associato all'id account
			query = "SELECT * "
					+ "FROM Ricercatore JOIN Account ON"
					+ " Ricercatore.id_account = " + id_account + ";";

			st = (Statement) con.createStatement();
			rs = st.executeQuery(query);

			if(rs.next()) {

				account = new Ricercatore(
						id_account,
						rs.getString(DBNames.ACC_USER),
						rs.getString(DBNames.ACC_PSW),
						rs.getString(DBNames.ACC_TIPOLOGIA),
						rs.getString(DBNames.RICER_NOME),
						rs.getString(DBNames.RICER_COGN),
						rs.getString(DBNames.RICER_COD_FISC),
						rs.getDate(DBNames.RICER_DATA_NASC),
						rs.getString(DBNames.RICER_CITTA_NASC),
						rs.getString(DBNames.RICER_SEX),
						rs.getString(DBNames.RICER_INDIRIZZO),
						rs.getString(DBNames.RICER_EMAIL),
						rs.getString(DBNames.RICER_RUOLO),
						rs.getString(DBNames.RICER_MATRIC),
						rs.getInt(DBNames.RICER_ANNO_MES_RUOLO),
						rs.getInt(DBNames.RICER_DIPART),
						rs.getInt(DBNames.RICER_AREA_SCI)
						);
			}
		}

		else if(tipologia.equals("amministratore")) {
			//La query restituisce i campi dell'amministratore associato all'id account
			query = "SELECT * "
					+ "FROM Amministratore JOIN Account ON"
					+ " Amministratore.id_account = " + id_account + ";";

			st = (Statement) con.createStatement();
			rs = st.executeQuery(query);

			if(rs.next()) {

				account = new Amministratore(
						id_account,
						rs.getString(DBNames.ACC_USER),
						rs.getString(DBNames.ACC_PSW),
						rs.getString(DBNames.ACC_TIPOLOGIA),
						rs.getString(DBNames.RICER_NOME),
						rs.getString(DBNames.RICER_COGN)
						);
			}
		}

		else {
			//La query restituisce i campi del responsabile di ateneo associato all'id account
			query = "SELECT * "
					+ "FROM Responsabile_ateneo JOIN Account ON"
					+ " Responsabile_ateneo.id_account = " + id_account + ";";

			st = (Statement) con.createStatement();
			rs = st.executeQuery(query);

			if(rs.next()) {

				account = new ResponsabileAteneo(
						id_account,
						rs.getString(DBNames.ACC_USER),
						rs.getString(DBNames.ACC_PSW),
						rs.getString(DBNames.ACC_TIPOLOGIA),
						rs.getString(DBNames.RESP_AT_NOME),
						rs.getString(DBNames.RESP_AT_COGNOME),
						rs.getInt(DBNames.RESP_AT_EVT)
						);
			}
		}

		rs.close();
		con.commit();
		st.close();
		DBConnectionPool.releaseConnection(con);

		return account;
	}
	
	/**
	 * 
	 * Is used to check if an account exists or not in our System
	 * 
	 * @param Account - The Account 
	 * 
	 * @return 0 if The Account doesn't exist, 1 if the account exists
	 * 
	 */
	public int notAnAccount(Account acc) {
		int test = 0;
		
		try{
			Connection con = DBConnectionPool.getConnection();

			String query="SELECT COUNT(*) as esisteAccount FROM account WHERE "
					+ "username='"+acc.getUsername()+"' AND password='"
					+acc.getPassword()+"' ";
			System.out.println("query : " +query);
			
			Statement st = (Statement) con.createStatement();
			
			ResultSet rs = st.executeQuery(query);
			
			if(rs.next()) test = rs.getInt("esisteAccount"); 
			
			rs.close();
			st.close();
			DBConnectionPool.releaseConnection(con);
			
		}catch (SQLException e) {
			e.printStackTrace();
		}
		
		return test;
	}
}
